package seismosurfer.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import seismosurfer.data.MacroData;
import seismosurfer.data.MagData;
import seismosurfer.data.SiteData;
import seismosurfer.data.constants.ParameterNames;
import seismosurfer.data.constants.QueryNames;
import seismosurfer.data.constants.TopNCategories;
import seismosurfer.util.SeismoException;

/**
 * A DAO class that encapsulates access to various views and tables.
 * Provides a way to make queries that combine many tables.
 *
 */
public class QueryDAO extends DAOObject implements ParameterNames, QueryNames,
        TopNCategories {

    /** Sites filtered by population */
    protected static final String SITES_QUERY = " SELECT * "
            + " FROM site_view s "
            + " WHERE s.population >= ? AND s.population <= ? ";

    /** Quakes with macroseismic data */
    protected static final String QUAKES_WITH_MACRO_QUERY =

    " SELECT * " + " FROM quake_mag_catalog q " + " WHERE q.has_macro = 1 ";

    /** Macroseismic data for a quake */
    protected static final String MACRO_QUERY =

    " SELECT * " + " FROM macro_view m " + " WHERE m.quakeid = ? ";

    /** Magnitudes for a specific quake */
    protected static final String MAG_QUERY = " SELECT magid, magnitude, magtype, calculated, mag_agency "
            + " FROM mag_view m " + " WHERE m.quakeid = ? ";

    /** Range Query */
    protected static final String RANGE_QUERY =

    " SELECT * " + " FROM quake_mag_catalog q "
            + " WHERE q.datetime >= ? AND q.datetime <= ? "
            + " AND q.depth >= ? AND q.depth <= ? "
            + " AND q.magnitude >= ? AND q.magnitude <= ? "
            + " AND SDO_RELATE(q.EPICENTER, " + "SDO_CS.VIEWPORT_TRANSFORM("
            + "mdsys.sdo_geometry(2003,0,NULL, "
            + "mdsys.sdo_elem_info_array(1,1003,3), "
            + "mdsys.sdo_ordinate_array( ?, ?, ?, ? )),8307),"
            + "'mask=INSIDE+COVEREDBY querytype=WINDOW') = 'TRUE'"
            + " AND q.source = ? ";

    /** Top-N Quakes Query */
    protected static final String TOP_N_QUERY = " SELECT * "
            + " FROM (SELECT * " + " FROM quake_mag_catalog q "
            + " WHERE q.datetime >= ? AND q.datetime <= ? "
            + " AND q.source = ? " + " AND SDO_RELATE(q.EPICENTER, "
            + " SDO_CS.VIEWPORT_TRANSFORM( "
            + " mdsys.sdo_geometry(2003,0,NULL, "
            + " mdsys.sdo_elem_info_array(1,1003,3),"
            + " mdsys.sdo_ordinate_array( ?, ?, ?, ? )),8307), "
            + " 'mask=INSIDE+COVEREDBY querytype=WINDOW') = 'TRUE' ";

    protected static final String TOP_QUERY_ORDER_BY =
    // " ORDER BY MAGNITUDE DESC "+
    " ) q " + " WHERE ROWNUM <= ? ";

    /** NN Query */

    // When using the SDO_NUM_RES parameter, no other constraints
    // are used in the WHERE clause. SDO_NUM_RES takes only proximity into
    // account.
    // For example, if you added a criterion to the WHERE clause because you
    // wanted
    // the five closest customers that resided in NY, and four of the five
    // closest
    // customers resided in NJ, the query above would return one row. This
    // behavior
    // is specific to the SDO_NUM_RES parameter, and its results may not be what
    // you
    // are looking for.

    // SDO_BATCH_SIZE is a tunable parameter that may affect your query's
    // performance.
    // SDO_NN internally calculates that number of distances at a time. The
    // initial
    // batch of rows returned may not satisfy the constraints in the WHERE
    // clause, so
    // the number of rows specified by SDO_BATCH_SIZE is continuously returned
    // until
    // all the constraints in the WHERE clause are satisfied. You should choose
    // a
    // SDO_BATCH_SIZE that initially returns the number of rows likely to
    // satisfy the
    // constraints in your WHERE clause.

    protected static final String NN_QUERY =

    " SELECT /* +ORDERED INDEX(q epicenter_idx) NO_INDEX (q pk_quake pk_catalog un_name un_url pk_mag un_quakeid_magtype) */ * "
            + " FROM quake_mag_catalog q "
            + " WHERE q.magnitude >= ?  AND q.source = ? "
            + " AND SDO_NN(q.EPICENTER, "
            + " MDSYS.SDO_GEOMETRY(2001, 8307, "
            + " MDSYS.SDO_POINT_TYPE( ?, ?, NULL), NULL, NULL), 'sdo_batch_size=0') = 'TRUE' "
            + " AND ROWNUM <= ? ORDER BY q.datetime ";

    /** Distance Based Query */
    protected static final String DISTANCE_QUERY =

    " SELECT * "
            + " FROM quake_mag_catalog q "
            + " WHERE "
            + " SDO_WITHIN_DISTANCE(q.EPICENTER, "
            + " MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE( ? , ?, NULL), NULL, NULL), "
            + " ?) = 'TRUE'" + // 'distance = ? unit=KM'
            " AND q.magnitude >= ? AND q.source = ? ORDER BY q.datetime ";

    /** Time Query */
    protected static final String TIME_QUERY =

    " SELECT * " + " FROM quake_mag_catalog q "
            + " WHERE q.datetime >= ? AND q.datetime <= ? "
            + " AND q.magnitude >= ? AND q.magnitude <= ? "
            + " AND q.longitude >= ? AND q.longitude <= ? "
            + " AND q.latitude >= ? AND q.latitude <= ? "
            + " AND q.source = ? " + " ORDER BY q.datetime";
    
    /** Magnitude values frequencies */
    protected static final String MAG_FREQUENCY = " select count(quakeid), magnitude "
            + " from quake_mag_catalog "
            + " where source = ? "
            + " group by magnitude ";
    
    /** Depth values frequencies */
    protected static final String DEPTH_FREQUENCY = " select count(quakeid), depth "
            + " from quake_mag_catalog "
            + " where source = ? "
            + " group by depth ";

    private Map parameters;
    
    /**
     * Default empty constructor.
     *
     */
    public QueryDAO() {
    }
    
    /**
     * Gets a map with the specified frequencies. The options are 
     * MAG_FREQUENCY and DEPTH_FREQUENCY.
     * 
     * @param source  the data source where the data come from
     * @param which   the type of frequency (magnitude or depth)
     * @return a Map with all the values/frequencies pairs
     */
    public Map getFrequencies(String source, String which) {
        Map freq = new HashMap(50);

        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {

            if (which.equalsIgnoreCase(MAG_FREQ)) {
                pstmt = DB.prepare(MAG_FREQUENCY);
            } else if (which.equalsIgnoreCase(DEPTH_FREQ)) {
                pstmt = DB.prepare(DEPTH_FREQUENCY);
            }

            pstmt.setString(1, source);

            rs = pstmt.executeQuery();

            while (rs.next()) {
                freq.put(new Double(rs.getDouble(2)), 
                         new Integer(rs.getInt(1)));
            }

            return freq;
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(pstmt, rs);
        }

    }
    
    /**
     * Loads a row from the database into a MagData object.
     * 
     * @param rs The resultset from which the row will be loaded.
     * @return A MagData object which contains the data of a row.
     * @throws SQLException
     */
    public static MagData loadMagData(ResultSet rs) throws SQLException {

        MagData md = new MagData();

        md.setMagnitudeID(rs.getLong(1));
        md.setMagnitude(rs.getDouble(2));
        md.setMagnitudeType(rs.getString(3));

        md.setCalculated(toBoolean(rs.getInt(4)));

        md.setMagnitudeAgency(rs.getString(5));

        return md;

    }
    
    /**
     * Gets a list with magnitude data that correspond to a
     * given quake`s id.
     * 
     * @param quakeID the id of an earthquake
     * @return a list of MagData
     */
    public List getMagResults(long quakeID) {

        List result = new ArrayList();

        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {

            stmt = DB.prepare(MAG_QUERY);
            stmt.setLong(1, quakeID);

            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(loadMagData(rs));
            }

            return result;
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }

    }
    
    /**
     * Loads a row from the database into a MacroData object.
     * 
     * @param rs The resultset from which the row will be loaded.
     * @return A MacroData object which contains the data of a row.
     * @throws SQLException
     */
    public static MacroData loadMacroData(ResultSet rs) throws SQLException {

        MacroData md = new MacroData();

        md.setCatalogName(rs.getString(1));

        md.setAzimuth(rs.getFloat(2));

        md.setDistance(rs.getFloat(3));
        md.setHypocenterDistance(rs.getFloat(4));

        md.setIntensity(rs.getFloat(5));
        md.setIntensityChar(rs.getString(6));

        md.setMacroseismicID(rs.getLong(7));

        md.setSiteNameGR(rs.getString(8));
        md.setSiteNameEN(rs.getString(9));
        md.setSiteNameGRKapod(rs.getString(10));

        md.setPrefectureGR(rs.getString(11));
        md.setPrefectureEN(rs.getString(12));

        md.setPopulation(rs.getInt(13));

        md.setSiteLongitude(rs.getFloat(14));
        md.setSiteLatitude(rs.getFloat(15));

        md.setCountryName(rs.getString(16));

        return md;
    }
    
    /**
     * Gets a list with macroseismic data that correspond to a
     * given quake`s id.
     * 
     * @param quakeID the id of an earthquake
     * @return a list of MacroData
     */
    public List getMacroResults(long quakeID) {

        PreparedStatement stmt = null;
        ResultSet rs = null;
        List result = new ArrayList();

        try {

            stmt = DB.prepare(MACRO_QUERY);
            stmt.setLong(1, quakeID);

            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(loadMacroData(rs));
            }
            return result;
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }

    }
    
    /**
     * Loads a row from the database into a SiteData object.
     * 
     * @param rs The resultset from which the row will be loaded.
     * @return A SiteData object which contains the data of a row.
     * @throws SQLException
     */
    public static SiteData loadSiteData(ResultSet rs) throws SQLException {

        SiteData sd = new SiteData();

        sd.setSiteCode(rs.getString(1));

        sd.setSiteLongitude(rs.getFloat(2));
        sd.setSiteLatitude(rs.getFloat(3));

        sd.setSiteNameGR(rs.getString(4));
        sd.setSiteNameEN(rs.getString(5));
        sd.setSiteNameGRKapod(rs.getString(6));

        sd.setPopulation(rs.getInt(7));

        sd.setPrefectureGR(rs.getString(8));
        sd.setPrefectureEN(rs.getString(9));

        sd.setCountryName(rs.getString(10));

        return sd;
    }
    
    /**
     * Gets a list with site data that are filtered 
     * according to some parameters.
     * 
     * @param parameters the Map that contains the parameter
     *        names/values pairs
     * @param maxRows  the maximum number of rows that can be retrieved
     * @return a list of SiteData
     */
    public List getSiteResults(Map parameters, int maxRows) {

        List result = new ArrayList();
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {

            stmt = DB.prepare(SITES_QUERY);

            // Set Max Rows - The maximum number of rows that a ResultSet object
            // can obtain
            stmt.setMaxRows(maxRows);

            stmt.setInt(1, ((Integer) parameters.get(POPULATION_MIN))
                    .intValue());
            stmt.setInt(2, ((Integer) parameters.get(POPULATION_MAX))
                    .intValue());

            // Get Results
            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(loadSiteData(rs));
            }

            return result;
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }

    }
    
    /**
     * Gets a list with quake data that are filtered 
     * according to the provided parameters and type
     * of query.
     * 
     * @param queryName  the name of query to be executed. 
     *        The queries names can be found in the QueryNames
     *        interface.
     * @param parameters   the Map that contains the parameter
     *        names/values pairs
     * @param maxRows  the maximum number of rows that can be retrieved
     * @return a list of QuakeData
     */
    public List getQuakeResults(String queryName, Map parameters, int maxRows) {

        this.parameters = parameters;

        String queryStr = "";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        List result = new ArrayList(100);

        try {

            // Select query to execute
            queryStr = getQueryString(queryName);

            stmt = DB.prepare(queryStr);

            // Set Max Rows - The maximum number of rows that a ResultSet object
            // can obtain
            stmt.setMaxRows(maxRows);

            // Set Parameters (values of placeholders)

            if (queryName.equalsIgnoreCase(QUAKES_WITH_MACRO)) {
                // Do nothing - this query has no parameters
            }

            else if (queryName.equalsIgnoreCase(RANGE)) {

                setRangeQueryParameters(parameters, stmt);

            } else if (queryName.equalsIgnoreCase(TOP_N)) {

                setTopNQueryParameters(parameters, stmt);
            } else if (queryName.equalsIgnoreCase(DISTANCE_BASED)) {

                setDistanceQueryParameters(parameters, stmt);
            } else if (queryName.equalsIgnoreCase(NN)) {
                setNNQueryParameters(parameters, stmt);
            } else if (queryName.equalsIgnoreCase(TIME)) {

                setTimeQueryParameters(parameters, stmt);
            } else {
                System.out.println("Wrong Query Name!!!");
            }

            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(QuakeDAO.loadQuake(rs));
            }

            return result;
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }

    }
    
    /**
     * Gets the query text given its name.
     * 
     * @param queryName the name of the query. One of the QueryNames
     *        constants.
     * @return the text that contains the query
     */
    protected String getQueryString(String queryName) {

        String queryStr = "";

        // Select query to execute

        if (queryName.equalsIgnoreCase(QueryNames.SITES)) {
            queryStr = SITES_QUERY;
        }

        else if (queryName.equalsIgnoreCase(QueryNames.QUAKES_WITH_MACRO)) {
            queryStr = QUAKES_WITH_MACRO_QUERY;
        }

        else if (queryName.equalsIgnoreCase(QueryNames.RANGE)) {

            queryStr = RANGE_QUERY;

        } else if (queryName.equalsIgnoreCase(QueryNames.TOP_N)) {

            queryStr = TOP_N_QUERY + getTopQueryString(this.parameters)
                    + TOP_QUERY_ORDER_BY;

        } else if (queryName.equalsIgnoreCase(QueryNames.DISTANCE_BASED)) {

            queryStr = DISTANCE_QUERY;

        } else if (queryName.equalsIgnoreCase(QueryNames.NN)) {

            queryStr = NN_QUERY;

        } else if (queryName.equalsIgnoreCase(QueryNames.TIME)) {

            queryStr = TIME_QUERY;

        } else {

            System.out.println("Wrong Query Name!!!");

        }

        return queryStr;

    }
    
    
    private String getTopQueryString(Map parameters) {

        String orderby = (String) parameters.get(ORDERBY);
        String order = "";

        if (orderby.equalsIgnoreCase(STRONG)) {

            order = " ORDER BY MAGNITUDE DESC ";

        } else if (orderby.equalsIgnoreCase(WEAK)) {

            order = " ORDER BY MAGNITUDE ASC ";

        } else if (orderby.equalsIgnoreCase(DEEP)) {

            order = " ORDER BY DEPTH DESC ";

        } else if (orderby.equalsIgnoreCase(SHALLOW)) {

            order = " ORDER BY DEPTH ASC ";

        } else if (orderby.equalsIgnoreCase(RECENT)) {

            order = " ORDER BY DATETIME DESC ";

        } else if (orderby.equalsIgnoreCase(OLD)) {

            order = " ORDER BY DATETIME ASC ";

        }

        return order;

    }

    private void setTopNQueryParameters(Map parameters, PreparedStatement stmt)
            throws SQLException {

        stmt.setTimestamp(1, toSQLTime((Long) parameters.get(TIME_MIN)));

        stmt.setTimestamp(2, toSQLTime((Long) parameters.get(TIME_MAX)));

        stmt.setString(3, ((String) parameters.get(SOURCE)));

        stmt.setDouble(4, ((Double) parameters.get(LONG_MIN)).doubleValue());
        stmt.setDouble(5, ((Double) parameters.get(LAT_MIN)).doubleValue());

        stmt.setDouble(6, ((Double) parameters.get(LONG_MAX)).doubleValue());
        stmt.setDouble(7, ((Double) parameters.get(LAT_MAX)).doubleValue());

        stmt.setInt(8, ((Integer) parameters.get(ROWNUM)).intValue());

    }

    private void setDistanceQueryParameters(Map parameters,
            PreparedStatement stmt) throws SQLException {

        stmt.setDouble(1, ((Double) parameters.get(LONG)).doubleValue());
        stmt.setDouble(2, ((Double) parameters.get(LAT)).doubleValue());

        int within_Distance_Value = ((Integer) parameters.get(DISTANCE))
                .intValue();
        String within_Distance = "distance = " + within_Distance_Value
                + " unit=KM ";
        stmt.setString(3, within_Distance);

        stmt.setDouble(4, ((Double) parameters.get(MAG_MIN)).doubleValue());

        stmt.setString(5, ((String) parameters.get(SOURCE)));

    }

    private void setTimeQueryParameters(Map parameters, PreparedStatement stmt)
            throws SQLException {

        stmt.setTimestamp(1, toSQLTime((Long) parameters.get(TIME_MIN)));

        stmt.setTimestamp(2, toSQLTime((Long) parameters.get(TIME_MAX)));

        stmt.setDouble(3, ((Double) parameters.get(MAG_MIN)).doubleValue());
        stmt.setDouble(4, ((Double) parameters.get(MAG_MAX)).doubleValue());

        stmt.setDouble(5, ((Double) parameters.get(LONG_MIN)).doubleValue());
        stmt.setDouble(6, ((Double) parameters.get(LONG_MAX)).doubleValue());

        stmt.setDouble(7, ((Double) parameters.get(LAT_MIN)).doubleValue());
        stmt.setDouble(8, ((Double) parameters.get(LAT_MAX)).doubleValue());

        stmt.setString(9, ((String) parameters.get(SOURCE)));

    }

    private void setRangeQueryParameters(Map parameters, PreparedStatement stmt)
            throws SQLException {

        stmt.setTimestamp(1, toSQLTime((Long) parameters.get(TIME_MIN)));

        stmt.setTimestamp(2, toSQLTime((Long) parameters.get(TIME_MAX)));

        stmt.setDouble(3, ((Double) parameters.get(DEPTH_MIN)).doubleValue());
        stmt.setDouble(4, ((Double) parameters.get(DEPTH_MAX)).doubleValue());

        stmt.setDouble(5, ((Double) parameters.get(MAG_MIN)).doubleValue());
        stmt.setDouble(6, ((Double) parameters.get(MAG_MAX)).doubleValue());

        stmt.setDouble(7, ((Double) parameters.get(LONG_MIN)).doubleValue());
        stmt.setDouble(8, ((Double) parameters.get(LAT_MIN)).doubleValue());

        stmt.setDouble(9, ((Double) parameters.get(LONG_MAX)).doubleValue());
        stmt.setDouble(10, ((Double) parameters.get(LAT_MAX)).doubleValue());

        stmt.setString(11, ((String) parameters.get(SOURCE)));

        System.out.println(parameters.toString());

    }

    private void setNNQueryParameters(Map parameters, PreparedStatement stmt)
            throws SQLException {

        stmt.setDouble(1, ((Double) parameters.get(MAG_MIN)).doubleValue());

        stmt.setString(2, ((String) parameters.get(SOURCE)));

        stmt.setDouble(3, ((Double) parameters.get(LONG)).doubleValue());
        stmt.setDouble(4, ((Double) parameters.get(LAT)).doubleValue());

        stmt.setInt(5, ((Integer) parameters.get(ROWNUM)).intValue());

    }

}
